-- new_lending
declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])
declare @date_last_month nvarchar(100)= (SELECT DATEADD(m,-1,CONVERT(date,@date_update)) )

IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE o.xtype IN ('U') AND 
o.name = 'GIAI_NGAN')
DROP TABLE GIAI_NGAN

SELECT *, ROW_NUMBER() over(partition by cif order by dist_date) as RN into GIAI_NGAN
FROM SERVER74.BICDATA.DBO.LNTB_DISBURSEMENT A
WHERE((A.SEGMENT<>'KHCN' AND A.BRANCH_CODE NOT IN (SELECT BRANCH_CODE FROM PhuongAnh.dbo.BRANCHOFF))
OR (A.SEGMENT IS NULL AND LEFT(A.SECTOR,1)>1))

IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE o.xtype IN ('U') AND 
o.name = 'NEW_LENDING')
DROP TABLE NEW_LENDING

SELECT LEFT(CONVERT(VARCHAR(8),DIST_DATE,112),6) AS REPORT_DATE  , CHUONG_TRINH_SP,CIF,CHANNEL,DIST_DATE,BRANCH_CODE,
CASE WHEN CHUONG_TRINH_SP LIKE '%BIL%' OR CHUONG_TRINH_SP LIKE '%CLEAN%' THEN 'UNSECURED PRODUCT'
ELSE 'SECURED PRODUCT' END AS PRODUCT_PROGRAM,ACCTNO,BAL_QD INTO NEW_LENDING
FROM GIAI_NGAN
WHERE RN=1
AND DIST_DATE BETWEEN @date_last_month AND @date_update
AND SEGMENT <>'HH'
ORDER BY DIST_DATE

delete from TBL_NEW_PR_LOAN

insert TBL_NEW_PR_LOAN
SELECT A.*,B.CUS_NAME,B.DAO,B.VPB_INDUSTRY,B.INDUSTRY_NAME 
 FROM NEW_LENDING A
LEFT JOIN TBL_CUSTOMER B
ON A.CIF=B.CIF
WHERE CHANNEL  IS NULL